﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VSDBM.Code.dto;
using System.Data;
using MySql.Data.MySqlClient;

namespace VSDBM.Code.dao
{
    public class ReferenceDAO
    {
        public static String add(Reference reference)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();

                    SequenceDAO.add(reference.sequence);
                    DataSet ds = SequenceDAO.select("WHERE `idacession` ='" + reference.sequence.idacession, "'");
                    int idSequence = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    MySqlCommand query = new MySqlCommand("INSERT INTO references_sequences (idsequence,idorganism) VALUES (" + idSequence + "," + reference.idOrganism + ")", connection);
                    query.ExecuteNonQuery();
                    return "Added!";
                
            }
            catch (Exception e)
            {
                return "ERRO! - " + e.Message;
            }
        }

        public static int getId(Reference reference) 
        {
            int id = 0;
            DataSet ds1 = SequenceDAO.select("WHERE `idacession` ='" + reference.sequence.idacession, "'");
            int idSequence = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
            DataSet ds = ReferenceDAO.select("WHERE idsequence =" + idSequence + " AND idorganism =" + reference.idOrganism, "");
            id= Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            return id;
        }
       
        public static Boolean delete(Reference reference)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query1 = new MySqlCommand("DELETE FROM reference_features WHERE idseqref = " + reference.id + "", connection);
                query1.ExecuteNonQuery();
                MySqlCommand query = new MySqlCommand("DELETE FROM references_sequences WHERE id = " + reference.id + "", connection);
                query.ExecuteNonQuery();
                connection.Close();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public static DataSet select(String filters, String orderby)
        {

            DataSet dSet = new DataSet();
            try
            {
                MySqlConnection connection = Connection.getConnection();

                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT * FROM references_sequences " + filters + " " + orderby, connection);

                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

       


    }
}